﻿using Aspose.Cells;
using Aspose.Cells.Drawing;
using System.Collections;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

namespace Koala.Pro.Helpers
{
    public class AsposeExcelTools
    {
        public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
        {
            error = "";
            if (datatable == null)
            {
                error = "DataTableToExcel:datatable 为空";
                return false;
            }
            Workbook workbook = new Workbook(fromfile);
            Cells cells = workbook.Worksheets[0].Cells;
            int num = 0;
            foreach (DataRow row in datatable.Rows)
            {
                num++;
                try
                {
                    cells.InsertRow(beginRow);
                    for (int i = 0; i < colNameList.Count; i++)
                    {
                        string text = colNameList[i].ToString();
                        for (int j = 0; j < datatable.Columns.Count; j++)
                        {
                            if (text == datatable.Columns[j].ColumnName)
                            {
                                _ = row[datatable.Columns[j].ColumnName];
                                cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
                                break;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    error = error + " DataTableInsertToExcel: " + ex.Message;
                }
            }
            workbook.Save(fromfile);
            return true;
        }

        public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
        {
            error = "";
            try
            {
                if (datatable == null)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return false;
                }
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                Cells cells = worksheet.Cells;
                int num = 0;
                foreach (DataRow row in datatable.Rows)
                {
                    num++;
                    try
                    {
                        for (int i = 0; i < datatable.Columns.Count; i++)
                        {
                            if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
                            {
                                Image image = (Image)row[i];
                                using MemoryStream stream = new MemoryStream();
                                image.Save(stream, ImageFormat.Jpeg);
                                worksheet.Pictures.Add(num, i, stream);
                            }
                            else
                            {
                                cells[num, i].PutValue(row[i]);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        error = error + " DataTableToExcel: " + ex.Message;
                    }
                }
                workbook.Save(filepath);
                return true;
            }
            catch (Exception ex2)
            {
                error = error + " DataTableToExcel: " + ex2.Message;
                return false;
            }
        }

        public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
        {
            error = "";
            Workbook workbook = new Workbook();
            try
            {
                if (datatable == null)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return false;
                }
                Style style = workbook.CreateStyle();
                style.HorizontalAlignment = TextAlignmentType.Center;
                style.ForegroundColor = Color.FromArgb(153, 204, 0);
                style.Pattern = BackgroundType.Solid;
                style.Font.IsBold = true;
                int num = 0;
                for (int i = 0; i < datatable.Columns.Count; i++)
                {
                    DataColumn dataColumn = datatable.Columns[i];
                    string stringValue = dataColumn.Caption ?? dataColumn.ColumnName;
                    workbook.Worksheets[0].Cells[num, i].PutValue(stringValue);
                    workbook.Worksheets[0].Cells[num, i].SetStyle(style);
                }
                num++;
                foreach (DataRow row in datatable.Rows)
                {
                    for (int j = 0; j < datatable.Columns.Count; j++)
                    {
                        workbook.Worksheets[0].Cells[num, j].PutValue(row[j].ToString());
                    }
                    num++;
                }
                for (int k = 0; k < datatable.Columns.Count; k++)
                {
                    workbook.Worksheets[0].AutoFitColumn(k, 0, 150);
                }
                workbook.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
                workbook.Save(filepath);
                return true;
            }
            catch (Exception ex)
            {
                error = error + " DataTableToExcel: " + ex.Message;
                return false;
            }
        }

        public static bool DataSetToExcel(DataSet dataset, string filepath, out string error)
        {
            if (DataTableToExcel(dataset.Tables[0], filepath, out error))
            {
                return true;
            }
            error = "DataTableToExcel: " + error;
            return false;
        }

        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
        {
            bool exportColumnName = true;
            return ExcelFileToDataTable(filepath, out datatable, exportColumnName, out error);
        }

        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, bool exportColumnName, out string error)
        {
            error = "";
            datatable = null;
            try
            {
                if (!File.Exists(filepath))
                {
                    error = "文件不存在";
                    datatable = null;
                    return false;
                }
                Worksheet worksheet = new Workbook(filepath).Worksheets[0];
                datatable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
                datatable.TableName = worksheet.Name;
                PictureCollection pictures = worksheet.Pictures;
                if (pictures.Count > 0)
                {
                    string error2 = "";
                    if (!InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2))
                    {
                        error += error2;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }

        public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, out string error)
        {
            bool exportColumnName = true;
            return ExcelFileToDataTables(filepath, out datatables, exportColumnName, out error);
        }

        public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, bool exportColumnName, out string error)
        {
            error = "";
            datatables = null;
            int num = 0;
            try
            {
                if (!File.Exists(filepath))
                {
                    error = "文件不存在";
                    datatables = null;
                    return false;
                }
                Workbook workbook = new Workbook(filepath);
                num = workbook.Worksheets.Count;
                datatables = new DataTable[num];
                for (int i = 0; i < num; i++)
                {
                    Worksheet worksheet = workbook.Worksheets[i];
                    try
                    {
                        datatables[i] = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
                        datatables[i].TableName = worksheet.Name;
                        PictureCollection pictures = worksheet.Pictures;
                        if (pictures.Count > 0)
                        {
                            string error2 = "";
                            if (!InsertPicturesIntoDataTable(pictures, datatables[i], out datatables[i], out error2))
                            {
                                error += error2;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        error = ex.Message;
                    }
                }
                return true;
            }
            catch (Exception ex2)
            {
                error = ex2.Message;
                return false;
            }
        }

        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, bool exportColumnName, out string error)
        {
            error = "";
            datatable = null;
            try
            {
                if (!File.Exists(filepath))
                {
                    error = "文件不存在";
                    datatable = null;
                    return false;
                }
                Worksheet worksheet = new Workbook(filepath).Worksheets[0];
                datatable = worksheet.Cells.ExportDataTableAsString(iFirstRow, iFirstCol, rowNum + 1, colNum + 1, exportColumnName);
                datatable.TableName = worksheet.Name;
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }

        public static bool ExcelFileToDataSet(string filepath, out DataSet dataset, out string error)
        {
            dataset = new DataSet();
            DataTable[] datatables = null;
            bool result = false;
            if (ExcelFileToDataTables(filepath, out datatables, out error))
            {
                dataset.Tables.AddRange(datatables);
                if (!string.IsNullOrEmpty(error))
                {
                    error = "ExcelFileToDataSet: " + error;
                }
                else
                {
                    result = true;
                }
            }
            else
            {
                error = "ExcelFileToDataSet: " + error;
                result = false;
            }
            return result;
        }

        public static bool GetPicturesFromExcelFile(string filepath, out PictureCollection[] pictures, out string error)
        {
            error = "";
            pictures = null;
            try
            {
                if (!File.Exists(filepath))
                {
                    error = "文件不存在";
                    pictures = null;
                    return false;
                }
                Workbook workbook = new Workbook(filepath);
                pictures = new PictureCollection[workbook.Worksheets.Count];
                for (int i = 0; i < workbook.Worksheets.Count; i++)
                {
                    pictures[i] = workbook.Worksheets[i].Pictures;
                }
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }

        private static bool InsertPicturesIntoDataTable(PictureCollection pictures, DataTable fromdatatable, out DataTable datatable, out string error)
        {
            error = "";
            datatable = fromdatatable;
            DataRow[] array = datatable.Select();
            foreach (Picture picture in pictures)
            {
                try
                {
                    //Console.WriteLine(picture.GetType().ToString());
                    using MemoryStream memoryStream = new MemoryStream();
                    memoryStream.Write(picture.Data, 0, picture.Data.Length);
                    Image value = Image.FromStream(memoryStream);
                    array[picture.UpperLeftRow][picture.UpperLeftColumn] = value;
                }
                catch (Exception ex)
                {
                    error = error + " InsertPicturesIntoDataTable: " + ex.Message;
                }
            }
            return true;
        }

        public static bool ExcelFileToLists(string filepath, out IList[] lists, out string error)
        {
            error = "";
            lists = null;
            DataTable datatable = new DataTable();
            new ArrayList();
            if (ExcelFileToDataTable(filepath, out datatable, out error) && GetPicturesFromExcelFile(filepath, out PictureCollection[] pictures, out error))
            {
                IList[] array = new ArrayList[datatable.Rows.Count];
                lists = array;
                int num = 0;
                foreach (DataRow row in datatable.Rows)
                {
                    lists[num] = new ArrayList(datatable.Columns.Count);
                    for (int i = 0; i <= datatable.Columns.Count - 1; i++)
                    {
                        lists[num].Add(row[i]);
                    }
                    num++;
                }
                for (int j = 0; j < pictures.Length; j++)
                {
                    foreach (Picture item in pictures[j])
                    {
                        try
                        {
                            if (item.UpperLeftRow <= datatable.Rows.Count && item.UpperLeftColumn <= datatable.Columns.Count)
                            {
                                lists[item.UpperLeftRow][item.UpperLeftColumn] = item.Data;
                            }
                        }
                        catch (Exception ex)
                        {
                            error += ex.Message;
                        }
                    }
                }
                return true;
            }
            return false;
        }

        public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
        {
            error = "";
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;
            int num = 0;
            worksheet.Pictures.Clear();
            cells.Clear();
            foreach (IList list in lists)
            {
                for (int j = 0; j <= list.Count - 1; j++)
                {
                    try
                    {
                        //Console.WriteLine(j + "  " + list[j].GetType());
                        if (list[j].GetType().ToString() == "System.Drawing.Bitmap")
                        {
                            Image image = (Image)list[j];
                            using MemoryStream stream = new MemoryStream();
                            image.Save(stream, ImageFormat.Jpeg);
                            worksheet.Pictures.Add(num, j, stream);
                        }
                        else
                        {
                            cells[num, j].PutValue(list[j]);
                        }
                    }
                    catch (Exception ex)
                    {
                        error += ex.Message;
                    }
                }
                num++;
            }
            workbook.Save(filepath);
            return true;
        }

        public static string ExportWithDataSource(string templateFile, string saveFileName, Dictionary<string, object> datasource)
        {
            if (!File.Exists(templateFile))
            {
                throw new ArgumentException(templateFile, $"{Path.GetFileName(templateFile)} 文件不存在，");
            }
            WorkbookDesigner workbookDesigner = new WorkbookDesigner();
            workbookDesigner.Workbook = new Workbook(templateFile);
            foreach (string key in datasource.Keys)
            {
                workbookDesigner.SetDataSource(key, datasource[key]);
            }
            workbookDesigner.Process();
            string text = FileDialogHelper.SaveExcel(saveFileName, "C:\\");
            if (!string.IsNullOrEmpty(text))
            {
                workbookDesigner.Workbook.Save(text, SaveFormat.Excel97To2003);
            }
            return text;
        }

        public static string ExportWithReplace(string templateFile, string saveFileName, Dictionary<string, string> dictReplace)
        {
            if (!File.Exists(templateFile))
            {
                throw new ArgumentException(templateFile, $"{Path.GetFileName(templateFile)} 文件不存在，");
            }
            WorkbookDesigner workbookDesigner = new WorkbookDesigner();
            workbookDesigner.Workbook = new Workbook(templateFile);
            Worksheet worksheet = workbookDesigner.Workbook.Worksheets[0];
            foreach (string key in dictReplace.Keys)
            {
                worksheet.Replace(key, dictReplace[key]);
            }
            workbookDesigner.Process();
            string text = FileDialogHelper.SaveExcel(saveFileName, "C:\\");
            if (!string.IsNullOrEmpty(text))
            {
                workbookDesigner.Workbook.Save(text, SaveFormat.Excel97To2003);
            }
            return text;
        }
    }
}
